<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="mybatis.mappers.ProductMapper">
    <resultMap id="product" type="models.entities.ProductEntity">
        <result property="id" column="productId"/>
        <result property="title" column="title"/>
        <result property="description" column="description"/>
        <result property="price" column="price"/>
        <result property="discountPrice" column="discountPrice"/>
        <result property="defaultImageId" column="defaultImageId"/>
        <result property="imagesCount" column="imagesCount"/>
    </resultMap>
    <resultMap id="category" type="models.entities.CategoryEntity">
        <result property="id" column="categoryId" />
        <result property="title" column="title" />
        <result property="rangeStart" column="leftValue" />
        <result property="rangeEnd" column="rightValue" />
        <result property="parentCategoryId" column="parentCategoryId" />
    </resultMap>
    <resultMap id="brandAutocomplete" type="models.entities.AutocompleteEntity">
        <result property="id" column="brandId"/>
        <result property="value" column="title"/>
    </resultMap>
    <resultMap id="picture" type="models.entities.PictureEntity">
        <result property="id" column="imageId"/>
        <result property="filePath" column="filePath"/>
    </resultMap>
    <resultMap id="brand" type="models.entities.BrandEntity">
        <result property="id" column="brandId"/>
        <result property="title" column="title"/>
    </resultMap>
    <delete id="deleteImage">
        delete from product_images where imageId = #{imageId};
        delete from images where imageId = #{imageId};
    </delete>
    <select id="searchBrandList" resultMap="brandAutocomplete">
        select * from Brands where title like #{q};
    </select>
    <select id="getAllProductImages" resultMap="picture">
        select i.* from images i, product_images pi where pi.imageId = i.imageId and pi.productId = #{productId};
    </select>
    <select id="getProduct" resultMap="product">
        select *, (select count(imageId) from product_images where productId = #{id}) as imagesCount from products where
        productId = #{id};
        update products set
        visitCounter = visitCounter+1
        where productId = #{id};
    </select>
    <select id="getBrandList" resultMap="brand" parameterType="int">
        select * from (select
        b.brandId,
        (select count(imageId) from brand_images where brandId = b.brandId) as imagesCount,
        b.title,
        count(b.title) as productCount
        from products p
        left join products_categories pc on pc.productId = p.productId
        left join brands b on b.brandId = p.brandId
        <![CDATA[ where pc.categoryId in (select categoryId from categories where leftValue >= #{rangeStart} and rightValue <= #{rangeEnd}) ]]>
        group by b.title) p order by productCount desc limit #{brandIndex}, 5;
    </select>
    <select id="getDefaultProductImage2" parameterType="int" resultMap="picture">
        set @imageId := (select imageId from product_images where productId = #{id} limit 1);
        set @imageId := (select
        if(p.defaultImageId is null, @imageId, p.defaultImageId) as ImageId
        from products p where p.productId = #{id});
        select imageId, filePath from images where imageId = @imageId limit 1;
    </select>
    <select id="getImagesCount" resultType="int">
        select count(imageId) from product_images where productId = #{productId};
    </select>
    <select id="getDefaultProductImage" parameterType="int" resultMap="picture">
        select i.imageId,
        i.filePath
        from products p
        left join product_images pi on pi.productId = p.productId
        left join images i on i.imageId = pi.imageId
        where p.productId = #{id} limit 1;
    </select>
    <select id="getRecentProducts" resultMap="product">
        select p.*,
        (select count(imageId) from product_images where productId = p.productId) as imagesCount
        from products p
        where addedDate > DATE_SUB(CURDATE(), INTERVAL 15 DAY)
        <if test="brandId > 0">
            and p.brandId = #{brandId}
        </if>
        order by addedDate desc LIMIT #{offset}, #{pageSize};
    </select>
    <select id="getRecentProductsBrandList" resultMap="brand">
        select * from (select
        b.brandId,
        (select count(imageId) from brand_images where brandId = b.brandId) as imagesCount,
        b.title,
        count(b.title) as productCount
        from products p
        left join products_categories pc on pc.productId = p.productId
        left join brands b on b.brandId = p.brandId
        where p.addedDate > DATE_SUB(CURDATE(), INTERVAL 15 DAY)
        group by b.title) p order by productCount desc
    </select>
    <select id="getRecentProductsTotalCount" resultType="int">
        select count(*)
        from products
        where addedDate > DATE_SUB(CURDATE(), INTERVAL 15 DAY)
        <if test="brandId > 0">
            and brandId = #{brandId}
        </if>
        ;
    </select>
    <select id="getDefaultBrandImage" parameterType="int" resultMap="picture">
        select i.imageId, i.filePath
        from brand_images bi,
        images i
        where i.imageId = bi.imageId and bi.brandId = #{id};
    </select>
    <select id="getBrandImage" resultMap="picture">
        select i.imageId, i.filePath
        from brand_images bi,
        images i
        where i.imageId = bi.imageId and bi.brandId = #{brandId}
        limit #{imageNumber}, 1;
    </select>

    <select id="getProductImage" resultMap="picture">
        select i.imageId,
        i.filePath
        from product_images pi, images i
        where pi.productId = #{productId} and i.imageId = pi.imageId
        limit #{imageNumber}, 1;
    </select>
    <insert id="create" parameterType="models.entities.ProductEntity">
        insert into
        products (title, description, price, discountPrice, brandId, shortDescription, createdByUser)
        values (#{title}, #{description}, #{price}, #{discountPrice}, #{brandId}, #{shortDescription},
        #{createdByUserId});
        <selectKey keyProperty="id" resultType="int" order="AFTER">
            select last_insert_id() as productId

        </selectKey>
    </insert>
    <update id="update" parameterType="models.entities.ProductEntity">
        update products
            set
                description = #{description},
                shortDescription = #{shortDescription},
                price = #{price}
            where
                productId = #{id};
    </update>
    <update id="updatePrice" parameterType="models.entities.ProductEntity">
        update products
            set
                price = #{price},
                discountPrice = #{discountPrice}
            where
                productId = #{id};
    </update>
    <select id="searchProducts" resultMap="product">
        select
        p.*,
        (select count(imageId) from product_images where productId = p.productId) as imagesCount
        from products p
        where p.title like #{query}
        <if test="brandId > 0">
            and p.brandId = #{brandId}
        </if>
        limit #{offset}, #{pageSize};
    </select>
    <select id="searchBrands" resultMap="brand">
        select * from
        (select b.brandId, b.title,
        (select count(imageId) from brand_images where brandId = b.brandId) as imagesCount,
        count(b.title) as productCount from products p
        left join brands b on b.brandId = p.brandId
        where p.title like #{query}
        <if test="brandId > 0">
            and p.brandId = #{brandId}
        </if>
        group by b.title) p order by productCount desc
    </select>
    <select id="getSearchProductsTotalCount" resultType="int">
        select count(productId) from products where title like #{query}
        <if test="brandId > 0">
            and brandId = #{brandId}
        </if>
    </select>
    <select id="getProductList" parameterType="models.entities.PagingFilter" resultMap="product">
        <![CDATA[
        select p.productId,
        p.title,
        p.shortDescription,
        p.price,
        (select count(imageId) from product_images where productId = p.productId) as imagesCount,
        p.discountPrice,
        p.defaultImageId
        from products p,
            products_categories pc
        where pc.categoryId in (select categoryId from categories where leftValue >= #{rangeStart} and rightValue <= #{rangeEnd})
        and p.productId = pc.productId
        ]]>
        <if test="brandId > 0">
            and p.brandId = #{brandId}
        </if>
        <![CDATA[
        LIMIT #{offset}, #{pageSize};
        ]]>

    </select>
    <select id="getProductsTotalCount" parameterType="int" resultType="int">
        <![CDATA[
        select count(p.productId)
        from products p,
        products_categories pc
        where pc.categoryId in (select categoryId from categories where leftValue >= #{rangeStart} and rightValue <= #{rangeEnd}) and p.productId = pc.productId
        ]]>
        <if test="brandId > 0">
            and p.brandId = #{brandId}
        </if>

    </select>
    <select id="getBrand" parameterType="java.lang.String" resultMap="brand">
        select brandId, title from brands where title = #{value};
    </select>
    <select id="getCategoryByTitle" parameterType="java.lang.String" resultMap="category">
        select * from
        (select * from categories where title = #{title}
            UNION
        select * from categories where categoryId in
            (select categoryId from category_aliases where alias = #{title})
        ) t;
    </select>
    <select id="isBrandExists" parameterType="java.lang.String" resultType="java.lang.Integer">
        select brandId from brands where title = #{value} limit 1;
    </select>
    <select id="isProductExists" parameterType="java.lang.String" resultType="java.lang.Integer">
        select productId from products where upper(title) = #{title} limit 1;
    </select>
    <select id="getByTitle" resultMap="product">
        select * from products where upper(title) = #{title} limit 1;
    </select>
    <insert id="createBrand" parameterType="models.entities.BrandEntity">
        insert into brands (title) values ( #{title} );

        <selectKey keyProperty="id" resultType="int" order="AFTER">
            select last_insert_id() as brandId
        </selectKey>
    </insert>
    <update id="updateProductSource" parameterType="models.entities.ProductSourceEntity">
        update product_sources
            set
                pricesAreEqual = #{pricesAreEqual}
        where sourceId = #{sourceId} and crawlerName = #{crawlerName};
    </update>
    <insert id="addProductSource" parameterType="models.entities.ProductSourceEntity">
        insert into
            product_sources(
                sourceId,
                crawlerName,
                url,
                title,
                productId,
                pricesAreEqual,
                shortDescriptionsAreEqual,
                descriptionsAreEqual,
                contentDownloaded)
            values(
                #{sourceId},
                #{crawlerName},
                #{url},
                #{title},
                #{productId},
                #{pricesAreEqual},
                #{shortDescriptionsAreEqual},
                #{descriptionsAreEqual},
                #{contentDownloaded});
    </insert>
    <select id="getProductSources" resultType="models.entities.ProductSourceEntity">
        select * from product_sources where productId = #{productId};
    </select>
    <select id="getProductSource" resultType="models.entities.ProductSourceEntity">
        select * from product_sources where crawlerName = #{crawlerName} and sourceId = #{sourceId};
    </select>
    <insert id="putInCategory" parameterType="models.entities.ProductEntity">
        insert into
        products_categories (productId, categoryId)
        select #{id}, #{categoryId} from dual where not exists
        (select * from products_categories where productId = #{id} and categoryId = #{categoryId});
    </insert>
    <insert id="linkToDistributor" parameterType="models.entities.ProductEntity">
        insert into distributor_products(productId, distributorId) values (#{id}, #{distributorId});
    </insert>
    <select id="getDistributorProduct" parameterType="java.lang.String" resultMap="product">
        select * from distributor_products where distributorId = #{value};
    </select>
    <select id="productHasPicture" parameterType="int" resultType="java.lang.String">
        select imageId
        from product_images where productId = #{value};
    </select>
    <insert id="addPicture" parameterType="models.entities.PictureEntity">
        insert into images(filePath, md5)
        values (#{filePath}, #{md5});
        set @imageId := (select last_insert_id() as imageId);
        insert into product_images(imageId, productId) values (@imageId, #{parentId});
    </insert>
    <insert id="addExistingPictureToProduct">
        insert into product_images(imageId, productId) values (#{imageId}, #{productId});
    </insert>
    <insert id="addPictureToBrand" parameterType="models.entities.PictureEntity">
        insert into images(filePath) select #{filePath} from dual where not exists
        (
        select imageId from images where filePath = #{filePath});
        set @imageId := (select imageId from images where filePath = #{filePath});
        insert into brand_images(imageId, brandId) select @imageId, #{parentId} from dual where not exists
        (select imageId from brand_images where imageId = @imageId and brandId = #{parentId});
    </insert>
</mapper>